package com.itbz.dao.Impl;

import com.itbz.dao.ScoreDao;
import com.itbz.pojo.Room;
import com.itbz.pojo.Score;
import com.itbz.util.JdbcDruidUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class ScreDaoImpl implements ScoreDao {
    /**
     * 实现添加录入
     * @param score
     * @return
     */
    @Override
    public int addScore(Score score) {
        Connection conn=null;
        PreparedStatement ps=null;
        int i=0;
        try {
            //连接数据库
            conn=JdbcDruidUtil.getConnection();
            ps= conn.prepareStatement("insert into score values (default,?,?,?,?)");
            ps.setObject(1,score.getSname());
            ps.setObject(2,score.getFirst());
            ps.setObject(3,score.getSecond());
            ps.setObject(4,score.getSrid());
            //执行sql语句
            i = ps.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JdbcDruidUtil.closeResource(ps,conn);
        }
        return i;
    }

    @Override
    public List<Score> findAll() {
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        List<Score>list=new ArrayList<>();
        try {
            //连接数据库
            conn=JdbcDruidUtil.getConnection();
            ps= conn.prepareStatement("select * from score JOIN room on score.srid=room.rid");
            //执行sql语句
            rs=ps.executeQuery();
            while (rs.next()){
                //创建Score对象
                Score score=new Score();
                //根据对象获取对应的值
                score.setSid(rs.getInt("sid"));
                score.setSname(rs.getString("sname"));
                score.setFirst(rs.getInt("first"));
                score.setSecond(rs.getInt("second"));
                score.setSrid(rs.getInt("srid"));
                //创建Room对象
                Room room=new Room();
                room.setRid(rs.getInt("rid"));
                room.setRname(rs.getString("rname"));
                //把班级对象保存到学生对象中
                score.setRoom(room);
                //把学生对象保存到集合中
                list.add(score);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JdbcDruidUtil.closeResource(rs,ps,conn);
        }
        return list;
    }
}
